/*##########Comment by owner: ##########*/
alter table Employers add DateOfCreation date null
go

update Employers set 
 DateOfCreation = getdate()
 go
go -- Application Delimiter

/*##########Comment by owner: ##########*/
if exists(select 1
		  from sysobjects
		  where name = 'Candidat_TypeHistory')
  drop table Candidat_TypeHistory
go

create table Candidat_TypeHistory
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  [Date] date not null,
  CandidateTypeId int not null,
  IsCandidate bit not null,
  IsContactPerson bit not null
)  
go

alter table Candidat_TypeHistory add constraint PK_Candidat_TypeHistory_ID primary key(Id) 
go


alter table Candidat_TypeHistory add constraint FK_Candidat_TypeHistory_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go

alter table Candidat_TypeHistory add constraint FK_Candidat_TypeHistory_Candidate_Types_CandidateTypeId 
      foreign key (CandidateTypeId) references Candidate_Types(Id) on delete cascade
go

insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
select Id, isnull(cast(DLC as date),getdate()), CandidateTypeId, IsCandidate, IsContactPerson
from Candidates
go


alter TRIGGER [dbo].[Candidates_ITrig] ON [dbo].[Candidates] AFTER Insert
AS 
BEGIN
  if @@rowcount = 0 return
  
  insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
  select i.Id, cast(getdate() as date), i.CandidateTypeId, i.IsCandidate, i.IsContactPerson
  from inserted i
   
  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.Id
end

GO


ALTER TRIGGER [dbo].[Candidates_UTrig] ON [dbo].[Candidates] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 

  insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
  select i.Id, cast(getdate() as date), i.CandidateTypeId, i.IsCandidate, i.IsContactPerson
  from inserted i
  join deleted d on d.Id = i.Id
  where isnull(i.CandidateTypeId,0) <> isnull(d.CandidateTypeId,0) or
 		isnull(i.IsCandidate,-1) <> isnull(d.IsCandidate,-1) or
		isnull(i.IsContactPerson,-1) <> isnull(d.IsContactPerson,-1) 
  
  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.ApproveOnPost,-1) <> isnull(d.ApproveOnPost,-1) or
		 isnull(i.ManagerId,0) <> isnull(d.ManagerId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.Territory_CompareImportantID,0) <> isnull(d.Territory_CompareImportantID,0) or
		 isnull(i.SalaryRange_CompareImportantID,0) <> isnull(d.SalaryRange_CompareImportantID,0) or
		 isnull(i.TerritoryID,0) <> isnull(d.TerritoryID,0) or
		 isnull(i.IsCandidate,-1) <> isnull(d.IsCandidate,-1) or
		 isnull(i.IsContactPerson,-1) <> isnull(d.IsContactPerson,-1) or
	     isnull(i.DocumentSum,0) <> isnull(d.DocumentSum,0) or
	     isnull(i.CandidateTypeId,0) <> isnull(d.CandidateTypeId,0) or
		 isnull(d.DateOfBirthDay,'1 jan 2000') <> isnull(i.DateOfBirthDay,'1 jan 2000') or
		 isnull(d.EnterDate,'1 jan 2000') <> isnull(i.EnterDate,'1 jan 2000') or
	     isnull(i.DocumnetNumber,'') <> isnull(d.DocumnetNumber,'') or
		 isnull(i.LastName,'') <> isnull(d.LastName,'') or
		 isnull(i.Adress,'') <> isnull(d.Adress,'') or
		 isnull(i.PrePaid,'') <> isnull(d.PrePaid,'') or
		 isnull(i.Education,'') <> isnull(d.Education,'') or
		 isnull(i.ResumeName,'') <> isnull(d.ResumeName,'') or
		 isnull(i.EducationType,'') <> isnull(d.EducationType,'') or
		 isnull(i.OnlyTradeMarks,'') <> isnull(d.OnlyTradeMarks,'') or
		 update(Characteristic) or
		 update(OtherReqirments) or
		 update(CommentForHistory)) and
		isnull(c.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))
end
GO



go -- Application Delimiter

/*##########Comment by owner: ##########*/
if exists(select 1
		  from sysobjects
		  where name = 'Vacancies_TypeHistory')
  drop table Vacancies_TypeHistory
go

create table Vacancies_TypeHistory
(
  Id int identity(1,1) not null,
  VacancyId int not null,
  [Date] date not null,
  VacancyTypeId int not null,
)  
go

alter table Vacancies_TypeHistory add constraint PK_Vacancies_TypeHistory_ID primary key(Id) 
go

alter table Vacancies_TypeHistory add constraint FK_Vacancies_TypeHistory_Vacancies_VacancyId 
      foreign key (VacancyId) references Vacancies(Id) on delete cascade
go

alter table Vacancies_TypeHistory add constraint FK_Vacancies_TypeHistory_Candidate_Types_CandidateTypeId 
      foreign key (VacancyTypeId) references Vacancy_Types(Id) on delete cascade
go

insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
select Id, isnull(cast(DLC as date),getdate()), VacancyTypeId
from Vacancies
go

ALTER TRIGGER [dbo].[Vacancies_ITrig] ON [dbo].[Vacancies] AFTER Insert
AS 
BEGIN
  if @@rowcount = 0 return	 

  insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
  select Id, cast(getdate() as date), VacancyTypeId
  from inserted i

  update v set
    v.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Vacancies v
  join inserted i on v.Id = i.Id
end

GO



ALTER TRIGGER [dbo].[Vacancies_UTrig] ON [dbo].[Vacancies] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 
  
  insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
  select i.Id, cast(getdate() as date), i.VacancyTypeId
  from inserted i
  join deleted d on d.Id = i.Id
  where isnull(i.VacancyTypeId,0) <> isnull(d.VacancyTypeId,0)

  update v set
    v.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Vacancies v
  join inserted i on v.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.EmployerID,0) <> isnull(d.EmployerID,0) or
		 isnull(i.PositionID,0) <> isnull(d.PositionID,0) or
		 isnull(i.EmployeeWhoFindCandidatesID,0) <> isnull(d.EmployeeWhoFindCandidatesID,0) or
		 isnull(i.EmployeeWhoApproveCandidatesID,0) <> isnull(d.EmployeeWhoApproveCandidatesID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.AgeMin,0) <> isnull(d.AgeMin,0) or
		 isnull(i.AgeMax,0) <> isnull(d.AgeMax,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.VacancyTypeId,0) <> isnull(d.VacancyTypeId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.Car_CompareImportantID,0) <> isnull(d.Car_CompareImportantID,0) or
		 isnull(i.Sex_CompareImportantID,0) <> isnull(d.Sex_CompareImportantID,0) or
		 isnull(i.Age_CompareImportantID,0) <> isnull(d.Age_CompareImportantID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.ExpirienceYear_CompareImportantID,0) <> isnull(d.ExpirienceYear_CompareImportantID,0) or
		 isnull(d.VacancyDate,'1 jan 2000') <> isnull(i.VacancyDate,'1 jan 2000') or
		 isnull(d.SalaryDate,'1 jan 2000') <> isnull(i.SalaryDate,'1 jan 2000') or
		 isnull(d.TrialPeriod,'1 jan 2000') <> isnull(i.TrialPeriod,'1 jan 2000') or
		 isnull(d.TradeMarks,'') <> isnull(i.TradeMarks,'') or
		 isnull(d.EducationType,'') <> isnull(i.EducationType,'') or
		 isnull(d.SalaryOnTrialPeriod,'') <> isnull(i.SalaryOnTrialPeriod,'') or
		 isnull(d.Education,'') <> isnull(i.Education,'') or
		 update(Comment) or Update(Comment1) or update(CommentForHistory) or
		 isnull(d.ExpirienceYear,0) <> isnull(i.ExpirienceYear,0)) and
		isnull(v.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))

END
GO

go -- Application Delimiter

/*##########Comment by owner: ##########*/
if exists(select 1
		  from sysobjects
		  where name = 'DayStatistics')
  drop table DayStatistics
go

create table DayStatistics
(
  Id int identity(1,1) not null,
  [Date] date not null,
  CandidatesWork int null,
  CandidatesActive int null,
  CandidateTrial int null,
  Vacancies int null,
  EmployersPerson int null,
  Employers int null,
  CV int null,
  Contacts int null
  
)  
go

alter table DayStatistics add constraint PK_DayStatistics_ID primary key(Id) 
go


create unique nonclustered index IX_DayStatistics_Date on DayStatistics
       ([Date] asc)
go


go -- Application Delimiter

/*##########Comment by owner: ##########*/
alter table SentEmailsHistory add IsContactInfoAttached bit null
go

update SentEmailsHistory set
  IsContactInfoAttached = 0
go

alter table SentEmailsHistory alter column IsContactInfoAttached bit not null
go
go -- Application Delimiter

/*##########Comment by owner: ##########*/




CREATE PROCEDURE [dbo].[sp_BackupCustom] (@FilePath nvarchar(1000)) 
AS
BEGIN
  declare @DB_Name varchar(255)
	      
  select @DB_Name = DB_NAME()
  
  BACKUP DATABASE @DB_Name TO DISK = @FilePath

END


GO



go -- Application Delimiter

/*##########Comment by owner: ##########*/
if exists(select 1
		  from sysobjects
		  where name = 'sp_PrepareStatistic')
  drop procedure [sp_PrepareStatistic]
go


CREATE PROCEDURE [dbo].[sp_PrepareStatistic] 
AS
BEGIN
  declare @LastDate Date,
		  @DateDiff int,
		  @TmpDate Date,
		  @CandidatesWork int,
		  @CandidatesActive int,
		  @CandidateTrial int,
		  @VacanciesActive int,
		  @EmployersPerson int,
		  @Employers int
  
  select @LastDate = max([Date])
  from DayStatistics ds 
  
  set @LastDate = isnull(@LastDate, dateadd(day, -1, getdate()))
  
  if @LastDate = cast(getdate() as date)
    set @LastDate = dateadd(day, -1, getdate())
 
  set @DateDiff = datediff(day, @LastDate, getdate())
  
  select
    @CandidatesWork = sum(case
						    when CandidateTypeId = 2 then 1
						    else 0
						  end),
	@CandidatesActive = sum(case
						    when CandidateTypeId = 4 then 1
						    else 0
						  end),
	@CandidateTrial = sum(case
						    when CandidateTypeId = 3 then 1
						    else 0
						  end)
  from Candidates
  where IsCandidate = 1
  
  select
    @EmployersPerson = count(1)
  from Candidates
  where IsContactPerson = 1
  
  select 
    @Employers = count(1)
  from Employers
  
  select
    @VacanciesActive = count(1)
  from Vacancies
  where VacancyTypeId = 4
  
 
  
  while (@DateDiff > 0) begin
    set @TmpDate = dateadd(day, @DateDiff, @LastDate)
    
    merge DayStatistics as target
    using (select 
			 @TmpDate,
			 @CandidatesWork,
			 @CandidatesActive,
			 @CandidateTrial,
			 @VacanciesActive,
			 @EmployersPerson,
			 @Employers,
			 (select count(distinct seh.CandidatId)
		      from SentEmailsHistory seh
			  where cast(seh.SendDate as date) = @TmpDate and
	                seh.IsContactInfoAttached = 0),
	         (select count(distinct seh.CandidatId)
		      from SentEmailsHistory seh
			  where cast(seh.SendDate as date) = @TmpDate and
	                seh.IsContactInfoAttached = 1)
		   ) 
		   as source ([Date], CandidatesWork, CandidatesActive, CandidateTrial, VacanciesActive,
					  EmployersPerson, Employers, SendResumes, SendContacts)
    on (target.Date = source.[Date])
    WHEN MATCHED THEN 
        UPDATE SET 
          [Date] = source.[Date],
          CandidatesWork = source.CandidatesWork,
          CandidatesActive = source.CandidatesActive,
          CandidateTrial = source.CandidateTrial,
          Vacancies = source.VacanciesActive,
		  EmployersPerson = source.EmployersPerson,
		  Employers = source.Employers,
		  CV = source.SendResumes,
		  Contacts = source.SendContacts
	WHEN NOT MATCHED THEN	
	    INSERT ([Date], CandidatesWork, CandidatesActive, CandidateTrial, Vacancies,
			    EmployersPerson, Employers, CV, Contacts)
	    VALUES (source.[Date], source.CandidatesWork, source.CandidatesActive, source.CandidateTrial,
	            source.VacanciesActive, source.EmployersPerson, source.Employers, source.SendResumes, source.SendContacts );
    
    set @DateDiff = @DateDiff - 1
  end 
 END


GO



go -- Application Delimiter

